<?php

namespace teach\service;

use Yii;

class Assemble
{

    /**
     * 获取拼团的产品列表
     * @param $where
     * @param $page
     * @param int $pre_page
     * @return array
     */
    public function assembleList($where, $page, $pre_page = 10)
    {
        $count = $this->assembleCount($where);
        $data = ['total' => 0, 'total_page' => 0, 'page' => 1, 'pre_page' => $pre_page, 'lists' => []];
        if ($count) {
            $total_page = ceil($count / $pre_page);
            $offset = ($page - 1) * $pre_page;
            $sql = "SELECT id, mid, title, img, price, start_time, end_time, count_sale FROM mall_assemble_product WHERE {$where} ORDER By sort, create_time ASC LIMIT {$offset}, {$pre_page}";
            $res = Yii::$app->db->createCommand($sql)->queryAll();
            $data = ['total' => $count, 'total_page' => $total_page, 'page' => $page, 'pre_page' => $pre_page, 'lists' => $res];
        }
        return $data;
    }

    /**
     * 统计拼团的产品
     * @param $where
     * @return false|null|string
     */
    private function assembleCount($where)
    {
        $sql = "SELECT COUNT(*) FROM mall_assemble_product WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->queryScalar();
    }

    public function assembleDetail($where, $andWhere)
    {
        $assemble = $this->assembleOne($where);
        if ($assemble['detail'] == null) {
            $assemble['detail'] = '';
        }
        if ($assemble['detail_text'] == null) {
            $assemble['detail_text'] = '';
        }
        $sku = $this->assembleSku($andWhere);

        if ($sku) {
            $assemble['sku'] = $sku;
            return $assemble;
        } else {
            return false;
        }
    }

    /**
     * 获取一条详产品拼团信息
     * @param $where
     * @return array|false
     */
    private function assembleOne($where)
    {
        $sql = "SELECT id, mid, title, img, imgs, detail, detail_text, freight, count_sale, group_num, start_time, end_time, effective_time, limited, label, price, old_price FROM mall_assemble_product WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 获取拼团产品sku信息
     * @param $andWhere
     * @return array
     */
    private function assembleSku($andWhere)
    {
        $sql = "SELECT * FROM mall_assemble_specs WHERE {$andWhere}";
        return Yii::$app->db->createCommand($sql)->queryAll();
    }

    /**
     * 获取团购列表
     * @param  $pid
     * @param $where
     * @param $page
     * @param $pre_page
     * @return array
     */
    public function assembleGroup($pid, $where, $page, $pre_page)
    {
        //查找出这个产品的组团时间限制 跟订单创建时间对比
        $sql_product = "SELECT id, group_num, limited, effective_time FROM mall_assemble_product WHERE id = {$pid}";
        $product = Yii::$app->db->createCommand($sql_product)->queryOne();
        $time = time() - $product['effective_time'];

        $count = $this->assembleGroupCount($where, $product['group_num'], $time);
        $data = ['total' => 0, 'total_page' => 0, 'page' => 1, 'pre_page' => $pre_page, 'lists' => []];

        if ($count) {
            $total_page = ceil( $count / $pre_page);
            $offset = ($page - 1) * $pre_page;
            $sql = "SELECT group_id,COUNT('group_id') AS count_times FROM mall_assemble_order WHERE {$where} AND create_time > {$time} AND status = 1 GROUP BY  group_id HAVING count_times < {$product['group_num']} LIMIT {$offset}, {$pre_page}";
            $res = Yii::$app->db->createCommand($sql)->queryAll();
            //$data = ['total' => $count, 'total_page' => $total_page, 'page' => $page, 'pre_page' => $pre_page, 'lists' => $res];

            $res_data = [];
            foreach ($res as $_key => $_val) {
                if ($_val['group_id'] == '') {
                    $res_data[$_key] = [];
                } else {
                    $group = Yii::$app->db->createCommand("SELECT id,avatar_url, nickname, phone, group_id, mode_id, create_time,effective_time FROM mall_assemble_order WHERE group_id = '{$_val['group_id']}' AND status = 1")->queryAll();
                    $res_data[$_key] = $group;
                }
            }

            $data = ['total' => $count, 'total_page' => $total_page, 'page' => $page, 'pre_page' => $pre_page, 'lists' => $res_data];

        }
        return $data;
    }

    /**
     * 统计不重复的记录数
     * @param $where
     * @param $count_times
     * @param $time
     * @return false|null|string
     */
    private function assembleGroupCount($where, $count_times, $time)
    {
        $sql = "SELECT COUNT(*) FROM (SELECT COUNT(*) FROM mall_assemble_order WHERE {$where} AND create_time > {$time} GROUP BY group_id HAVING COUNT(*) < {$count_times}) AS  d";
        return Yii::$app->db->createCommand($sql)->queryScalar();
    }

    /**
     * 查找是否已经申请过 并处理过了
     * @param $where
     * @return array|false
     */
    public function assembleRefundFind($where)
    {
        $sql = "SELECT COUNT(*) FROM mall_refund_reason WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->queryScalar();
    }

    /**
     * 插入退款申请
     * @param $map
     * @param $oid
     * @return int
     */
    public function assembleRefund($map, $oid, $out_trade_no)
    {
        $db = Yii::$app->db;
        $transaction = $db->beginTransaction();
        try {
            //更新订单表为退款状态
            $sqlTotal = "UPDATE mall_assemble_order SET status = -2 WHERE id = {$oid}";
            $db->createCommand($sqlTotal)->execute();
            //更新支付表表为退款状态
            $sqlTotalPay = "UPDATE mall_assemble_pay SET status = -1 WHERE out_trade_no = '{$out_trade_no}'";
            $db->createCommand($sqlTotalPay)->execute();
            //插入退款记录
            $db->createCommand()->insert('mall_refund_reason', $map)->execute();
            $transaction->commit();
            return true;
        } catch (\Exception $e) {
            $transaction->rollBack();
            return false;
        }

        //return Yii::$app->db->createCommand()->insert('mall_refund_reason', $map)->execute();
    }

    /**
     * 查找拼团订单是否存在
     * @param $where
     * @return array|false
     */
    public function assembleOrderFind($where)
    {
        $sql = "SELECT * FROM mall_assemble_order WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 获取一条用户信息
     * @param $where
     * @return array|false
     */
    public function assembleUser($where)
    {
        $sql = "SELECT id, nickname, phone FROM edu_user WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 查找一条订单详情
     * @param $andWhere
     * @return array|false
     */
    public function assembleOrderOne($andWhere)
    {
        $sql = "SELECT id, mid, uid, pid, sku_id, avatar_url, nickname, mode_id, group_id, out_trade_no, total_fee, phone, status, count, create_time FROM mall_assemble_order WHERE {$andWhere}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 查找出拼团产品
     * @param $whereFilter
     * @param $andWhereFilter
     * @return array|bool|false
     */
    public function assembleProduct($whereFilter, $andWhereFilter)
    {
        $assemble = $this->assembleOne($whereFilter);
        $sku = $this->assembleSku($andWhereFilter);

        if ($sku) {
            $assemble['sku'] = $sku;
            return $assemble;
        } else {
            return false;
        }
    }

    /**
     * 根据组id查出拼团组
     * @param $filter
     * @return int
     */
    public function assembleOrderGroup($filter)
    {
        $sql = "SELECT id, pid, sku_id, avatar_url, nickname, status, mode_id, group_id, create_time FROM mall_assemble_order WHERE {$filter} AND status = 1";
        return Yii::$app->db->createCommand($sql)->queryAll();
    }

}